package com.bw.bean;

import com.bw.dao.Emp;
import com.bw.dao.Ps;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;

@Mapper
public interface EmpDao {

    @Select("select b2.`name`,sum(i.final_total_amount) `value` from order_info i \n" +
            "LEFT JOIN order_detail d on d.order_id=i.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN base_category3 b3 on s.category3_id=b3.id\n" +
            "LEFT JOIN base_category2 b2 on b3.category2_id=b2.id\n" +
            "group by b2.`name` order by value asc")
    public List<Emp> getList1();

    @Select("select CONCAT(SUBSTRING(u.birthday,3,1),'0后') `name`,count(*) `value` from order_info o\n" +
            "LEFT JOIN user_info u on o.user_id=u.id\n" +
            "group by CONCAT(SUBSTRING(u.birthday,3,1),'0后')")
    List<Emp> getList2();

    @Select("select yys `name`,count(*) `value` from order_info where yys is not null group by yys \n")
    List<Emp> getList3();

    @Select("select \n" +
            "p.spu_name `name`,\n" +
            "if(u.gender='M','男','女') `sex`,\n" +
            "sum(o.final_total_amount) `value`\n" +
            "from user_info u\n" +
            "LEFT JOIN order_info o on o.user_id=u.id\n" +
            "LEFT JOIN order_detail d on d.order_id=o.id\n" +
            "LEFT JOIN sku_info s on d.sku_id=s.id\n" +
            "LEFT JOIN spu_info p on s.spu_id=p.id\n" +
            "where p.spu_name is not null\n" +
            "group by p.spu_name order by value desc\n")
    List<Ps> getList4();

}
